import os
import sqlite3
from pathlib import Path
import json
from datetime import datetime

class DatabaseManager:
    def __init__(self):
        # 获取系统用户目录
        app_data_dir = os.path.join(os.environ['LOCALAPPDATA'], 'UniversalClient')
        # 创建应用数据目录
        os.makedirs(app_data_dir, exist_ok=True)
        # 设置数据库文件路径
        self.db_path = os.path.join(app_data_dir, 'data.db')
        # 初始化数据库连接
        self.conn = None
        self.cursor = None

    def _convert_value(self, value, type_name):
        """转换值到指定类型
        Args:
            value: 原始值
            type_name: 目标类型名称 (string, number, json, time, date)
        Returns:
            转换后的值
        """
        if value is None:
            return None

        try:
            if type_name == 'string':
                return str(value) if value is not None else ''
            elif type_name == 'number':
                if isinstance(value, (int, float)):
                    return value
                if isinstance(value, str) and value.strip():
                    return float(value) if '.' in value else int(value)
                return 0
            elif type_name == 'json':
                if isinstance(value, (dict, list)):
                    return value
                if isinstance(value, str):
                    return json.loads(value)
                return {}
            elif type_name == 'time':
                if isinstance(value, (int, float)):
                    # 处理时间戳
                    dt = datetime.fromtimestamp(value)
                    return dt.strftime('%Y-%m-%d %H:%M:%S')
                elif isinstance(value, str):
                    # 尝试解析已经格式化的时间字符串
                    try:
                        dt = datetime.strptime(value, '%Y-%m-%d %H:%M:%S')
                        return value
                    except ValueError:
                        try:
                            dt = datetime.strptime(value, '%Y-%m-%d')
                            return dt.strftime('%Y-%m-%d %H:%M:%S')
                        except ValueError:
                            return value
                elif isinstance(value, datetime):
                    return value.strftime('%Y-%m-%d %H:%M:%S')
                return value
            elif type_name == 'date':
                if isinstance(value, (int, float)):
                    # 处理时间戳
                    dt = datetime.fromtimestamp(value)
                    return dt.strftime('%Y-%m-%d')
                elif isinstance(value, str):
                    # 尝试解析已经格式化的时间字符串
                    try:
                        dt = datetime.strptime(value, '%Y-%m-%d %H:%M:%S')
                        return dt.strftime('%Y-%m-%d')
                    except ValueError:
                        try:
                            dt = datetime.strptime(value, '%Y-%m-%d')
                            return value
                        except ValueError:
                            return value
                elif isinstance(value, datetime):
                    return value.strftime('%Y-%m-%d')
                return value
            return value
        except Exception as e:
            print(f"值转换错误: {e}, 值: {value}, 类型: {type_name}")
            if type_name == 'string':
                return ''
            elif type_name == 'number':
                return 0
            elif type_name == 'json':
                return {}
            elif type_name == 'time':
                return datetime.now().strftime('%Y-%m-%d %H:%M:%S')
            elif type_name == 'date':
                return datetime.now().strftime('%Y-%m-%d')
            return value

    def connect(self):
        """连接数据库"""
        try:
            self.conn = sqlite3.connect(self.db_path)
            self.cursor = self.conn.cursor()
            return True
        except sqlite3.Error as e:
            print(f"数据库连接错误: {e}")
            return False

    def disconnect(self):
        """关闭数据库连接"""
        if self.cursor:
            self.cursor.close()
        if self.conn:
            self.conn.close()
            self.conn = None
            self.cursor = None

    def execute(self, sql, params=None):
        """执行SQL语句"""
        try:
            if not self.conn:
                self.connect()
            if params:
                self.cursor.execute(sql, params)
            else:
                self.cursor.execute(sql)
            self.conn.commit()
            return True
        except sqlite3.Error as e:
            print(f"SQL执行错误: {e}")
            print(f"SQL语句: {sql}")
            print(f"参数: {params}")
            return False

    def query(self, sql, params=None, mapper=None):
        """查询数据
        Args:
            sql: SQL语句
            params: SQL参数
            mapper: 字段映射列表，格式为[{"field": "字段名", "type": "类型名"}]
                   类型可以是: string, number, json, time, date
        Returns:
            如果提供mapper，返回映射后的字典列表
            否则返回原始查询结果
        """
        try:
            if not self.conn:
                self.connect()
            if params:
                self.cursor.execute(sql, params)
            else:
                self.cursor.execute(sql)
            
            results = self.cursor.fetchall()
            
            # 如果没有提供mapper，直接返回结果
            if not mapper:
                return results
            
            # 使用mapper映射结果
            mapped_results = []
            for row in results:
                mapped_row = {}
                for i, field_map in enumerate(mapper):
                    # 确保索引在有效范围内
                    if i < len(row):
                        field_name = field_map['field']
                        field_type = field_map.get('type', 'string')  # 默认为string类型
                        mapped_row[field_name] = self._convert_value(row[i], field_type)
                mapped_results.append(mapped_row)
            return mapped_results
            
        except sqlite3.Error as e:
            print(f"查询错误: {e}")
            print(f"SQL语句: {sql}")
            print(f"参数: {params}")
            return [] if mapper else []

    def query_one(self, sql, params=None, mapper=None):
        """查询单条数据
        Args:
            sql: SQL语句
            params: SQL参数
            mapper: 字段映射列表，格式为[{"field": "字段名", "type": "类型名"}]
                   类型可以是: string, number, json, time, date
        Returns:
            如果提供mapper，返回映射后的字典
            否则返回原始查询结果
        """
        try:
            if not self.conn:
                self.connect()
            if params:
                self.cursor.execute(sql, params)
            else:
                self.cursor.execute(sql)
            
            result = self.cursor.fetchone()
            
            # 如果没有结果或没有提供mapper，直接返回结果
            if not result or not mapper:
                return result
            
            # 使用mapper映射结果
            mapped_result = {}
            for i, field_map in enumerate(mapper):
                # 确保索引在有效范围内
                if i < len(result):
                    field_name = field_map['field']
                    field_type = field_map.get('type', 'string')  # 默认为string类型
                    mapped_result[field_name] = self._convert_value(result[i], field_type)
            return mapped_result
            
        except sqlite3.Error as e:
            print(f"查询错误: {e}")
            print(f"SQL语句: {sql}")
            print(f"参数: {params}")
            return None

    def table_exists(self, table_name):
        """检查表是否存在"""
        sql = "SELECT name FROM sqlite_master WHERE type='table' AND name=?"
        result = self.query_one(sql, (table_name,))
        return result is not None

    def create_table(self, sql):
        """创建表"""
        return self.execute(sql)

    def __enter__(self):
        """上下文管理器入口"""
        self.connect()
        return self

    def __exit__(self, exc_type, exc_val, exc_tb):
        """上下文管理器出口"""
        self.disconnect()

# 创建单例实例
db = DatabaseManager()
